﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.GBMS.MODEL;
using KPIS.GERP.GBMS.DAL;

namespace KPIS.GERP.GBMS.DAL
{
    public sealed class ExpenseGroupDAL : IExpenseGroupDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public ExpenseGroupDAL()
        {

        }

        public IList<TreeViewInfo> TreeView(IList<TreeViewInfo> treeView, bool isRootNode)
        {
            List<TreeViewInfo> TreeView = new List<TreeViewInfo>();
            string strSQL;


            strSQL = "SELECT CONCAT('EXPENSEGROUP-', EG.BGM_EXP_GRP_SEQ) AS ID, EG.BGM_EXP_GRP_NAME AS NAME"
                      + ((isRootNode) ? ", CONCAT('EXPENSEGROUP-', EG.UPPER_BGM_EXP_GRP_SEQ)" : ", COALESCE(CONCAT('EXPENSEGROUP-', EG.UPPER_BGM_EXP_GRP_SEQ), CONCAT('SOURCE-', EG.BGM_SOURCE_SEQ))") + " AS PARENT"
                      + ", EG.BGM_EXP_GRP_NAME AS TEXT_FIELD, CONCAT('EXPENSEGROUP-', EG.BGM_EXP_GRP_SEQ) AS VALUE_FIELD"
                      + " FROM BGM_MAS_EXPENSE_GROUP EG"
                      + " WHERE EG.RECORD_STATUS not like @status";

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@status", SystemInfo._DeleteRecordStatus);

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    TreeViewInfo TreeViews = new TreeViewInfo();
                    TreeViews.ID = Convert.ToString(dr["BGM_EXP_GRP_SEQ"]);
                    TreeViews.Name = Convert.ToString(dr["BGM_EXP_GRP_NAME"]);
                    TreeViews.Parent = Convert.ToString(dr["PARENT"]);
                    TreeViews.TextField = Convert.ToString(dr["BGM_EXP_GRP_NAME"]);
                    TreeViews.ValueField = Convert.ToString(dr["VALUE_FIELD"]);
                }

                //while (rdr.Read())
                //{
                //    TreeViewInfo expGrp = new TreeViewInfo(rdr.GetString(0), rdr.GetString(1), (rdr.IsDBNull(2)) ? null : rdr.GetString(2), rdr.GetString(3), rdr.GetString(4));
                //    treeView.Add(expGrp);
                //}

                return treeView;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Insert(ExpenseGroupInfo expGrpInfo)
        {
            Nullable<int> parent = null;
            Nullable<int> source = null;

            if (expGrpInfo.Parent.Sequence > 0)
                parent = expGrpInfo.Parent.Sequence;

            if (expGrpInfo.Source.Sequence > 0)
                source = expGrpInfo.Source.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@code", expGrpInfo.Code);
                dbManager.AddParameters(1, "@name", expGrpInfo.Name);
                dbManager.AddParameters(2, "@descr", expGrpInfo.Description);
                dbManager.AddParameters(3, "@expGrpParent", parent);
                dbManager.AddParameters(4, "@Source", source);
                dbManager.AddParameters(5, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(6, "@gfmis_code", expGrpInfo.Gfmis_code);

                return dbManager.ExecuteNonQuery(CommandType.Text
                    , "INSERT INTO BGM_MAS_EXPENSE_GROUP"
                        + " (BGM_EXP_GRP_CODE, BGM_EXP_GRP_NAME, BGM_EXP_GRP_DESCR"
                            + ", UPPER_BGM_EXP_GRP_SEQ, BGM_SOURCE_SEQ, RECORD_STATUS, GFMIS_CODE)"
                        + " VALUES (@code, @name, @descr, @expGrpParent, @Source, @status, @gfmis_code)");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Update(ExpenseGroupInfo expGrpInfo)
        {
            string strSQL;
            Nullable<int> parent = null;
            Nullable<int> source = null;

            if (expGrpInfo.Parent.Sequence > 0)
                parent = expGrpInfo.Parent.Sequence;

            if (expGrpInfo.Source.Sequence > 0)
                source = expGrpInfo.Source.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@seq", expGrpInfo.Sequence);
                dbManager.AddParameters(1, "@code", expGrpInfo.Code);
                dbManager.AddParameters(2, "@name", expGrpInfo.Name);
                dbManager.AddParameters(3, "@descr", expGrpInfo.Description);
                dbManager.AddParameters(4, "@expGrpParent", parent);
                dbManager.AddParameters(5, "@Source", source);
                dbManager.AddParameters(6, "@status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(7, "@gfmis_code", expGrpInfo.Gfmis_code);

                strSQL = "UPDATE BGM_MAS_EXPENSE_GROUP"
                    + " SET BGM_EXP_GRP_CODE = @code"
                        + ", BGM_EXP_GRP_NAME = @name"
                        + ", BGM_EXP_GRP_DESCR = @descr"
                        + ", UPPER_BGM_EXP_GRP_SEQ = @expGrpParent"
                        + ", BGM_SOURCE_SEQ = @Source"
                        + ", RECORD_STATUS = @status"
                        + ", GFMIS_CODE = @gfmis_code"
                    + " WHERE BGM_EXP_GRP_SEQ = @seq";

                return dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public int Delete(int seq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@status", SystemInfo._DeleteRecordStatus);

                return dbManager.ExecuteNonQuery(CommandType.Text, "UPDATE BGM_MAS_EXPENSE_GROUP SET RECORD_STATUS = @status WHERE BGM_EXP_GRP_SEQ = @seq");
            }
            catch
            {
                return 0;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public IList<ExpenseGroupInfo> Search(ExpenseGroupInfo expGrpInfo)
        {
            string strSQL;
            int parent = 0;
            int source = 0;

            List<ExpenseGroupInfo> expGrpList = new List<ExpenseGroupInfo>();

            if (expGrpInfo.Parent != null)
                parent = expGrpInfo.Parent.Sequence;

            if (expGrpInfo.Source != null)
                source = expGrpInfo.Source.Sequence;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);

                dbManager.AddParameters(0, "@seq", expGrpInfo.Sequence);
                dbManager.AddParameters(1, "@code", expGrpInfo.Code);
                dbManager.AddParameters(2, "@name", expGrpInfo.Name);
                dbManager.AddParameters(3, "@descr", expGrpInfo.Description);
                dbManager.AddParameters(4, "@status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(5, "@parent", parent);
                dbManager.AddParameters(6, "@source", source);

                strSQL = "SELECT EG.BGM_EXP_GRP_SEQ, EG.BGM_EXP_GRP_CODE, EG.BGM_EXP_GRP_NAME, EG.BGM_EXP_GRP_DESCR"
                    + ", EG.RECORD_STATUS, EG.UPPER_BGM_EXP_GRP_SEQ, EG.BGM_SOURCE_SEQ, EG.GFMIS_CODE"
                    + " FROM BGM_MAS_EXPENSE_GROUP EG"
                    + " WHERE EG.RECORD_STATUS not like @status"
                    + ((expGrpInfo.Sequence > 0) ? " AND BGM_EXP_GRP_SEQ = @seq" : "")
                    + ((expGrpInfo.Code.Length > 0) ? " AND BGM_EXP_GRP_CODE = @code" : "")
                    + ((expGrpInfo.Name.Length > 0) ? " AND BGM_EXP_GRP_NAME = @name" : "")
                    + ((expGrpInfo.Description.Length > 0) ? " AND BGM_EXP_GRP_DESCR = @descr" : "")
                    + ((parent > 0) ? " AND UPPER_BGM_EXP_GRP_SEQ = @parent" : "")
                    + ((source > 0) ? " AND BGM_SOURCE_SEQ = @source" : "");

                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    ExpenseGroupInfo expGrpLists = new ExpenseGroupInfo();
                    expGrpLists.Sequence = Convert.ToInt32(dr["BGM_EXP_GRP_SEQ"]);
                    expGrpLists.Code = Convert.ToString(dr["BGM_EXP_GRP_CODE"]);
                    expGrpLists.Name = Convert.ToString(dr["BGM_EXP_GRP_NAME"]);
                    expGrpLists.Description = Convert.ToString(dr["BGM_EXP_GRP_DESCR"]);

                    expGrpList.Add(expGrpLists);
                }

                //while (rdr.Read())
                //{
                //    ExpenseGroupInfo expGrpParentInfo = new ExpenseGroupInfo();

                //    if (rdr.IsDBNull(5))
                //        expGrpParentInfo = null;
                //    else expGrpParentInfo.Sequence = rdr.GetInt32(5);

                //    SourceInfo srcInfo = new SourceInfo();

                //    if (rdr.IsDBNull(6))
                //        srcInfo = null;
                //    else srcInfo.Sequence = rdr.GetInt32(6);

                //    ExpenseGroupInfo expGrp = new ExpenseGroupInfo(rdr.GetInt32(0)
                //        , (rdr.IsDBNull(1)) ? "" : rdr.GetString(1)
                //        , (rdr.IsDBNull(2)) ? "" : rdr.GetString(2)
                //        , (rdr.IsDBNull(3)) ? "" : rdr.GetString(3)
                //        , rdr.GetString(4)
                //        , (rdr.IsDBNull(7)) ? "" : rdr.GetString(7)
                //        , expGrpParentInfo, srcInfo, null);

                //    expGrpList.Add(expGrp);
                //}

                return expGrpList;
            }
            catch
            {
                return null;
                throw;
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
        }

        public bool CheckDuplicateCode(string code)
        {
            string strSQL;

            bool result = false;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@deletestatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@bgm_exp_grp_code", code);

                strSQL = @"SELECT bgm_exp_grp_code 
                           FROM bgm_mas_expense_group
                           WHERE  bgm_exp_grp_code =  @bgm_exp_grp_code
                           AND  record_status not like @deletestatus";
                DataTable dtb = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dtb.Rows)
                {
                    result = true; // found duplicate
                    break;
                }

                //while (rdr.Read())
                //{
                //    result = true; // found duplicate
                //    break;
                //}
            }
            catch (Exception error)
            { }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }

            return result;
        }
        
    }
}
